import pymysql
import hashlib
from flask import Flask, request, jsonify

# 定义YQ类
class YQ:
    def __init__(self, publisher, time, location, total, reserved):
        self.publisher = publisher
        self.time = time
        self.location = location
        self.total = total
        self.reserved = reserved
        self.id = self.calculate_id()

    def calculate_id(self):
        data = f"{self.time}{self.location}{self.total}{self.publisher}"
        md5 = hashlib.md5(data.encode()).hexdigest()
        return md5

# 连接到MySQL数据库
def connect_to_database():
    db = pymysql.connect(
        host='127.0.0.1', 
        user='root', 
        passwd='123456', db='spiders')
    return db

# 创建YQ表
def create_table():
    db = connect_to_database()
    cursor = db.cursor()
    # 如果表存在，则删除表
    cursor.execute("DROP TABLE IF EXISTS yq")
    cursor.execute("CREATE TABLE IF NOT EXISTS yq (id VARCHAR(32) PRIMARY KEY, publisher VARCHAR(255), time VARCHAR(255), location VARCHAR(255), total INT, reserved INT)")
    db.close()
# 创建用户表
def create_users_table():
    db = connect_to_database()
    cursor = db.cursor()
    cursor.execute("CREATE TABLE IF NOT EXISTS yq_users (username1 VARCHAR(255) PRIMARY KEY, password1 VARCHAR(255))")
    db.close()
    
# 将YQ对象存储到数据库
def save_yq(yq):
    db = connect_to_database()
    cursor = db.cursor()
    #根据md5_id判断是否已经存在
    cursor.execute("SELECT * FROM yq WHERE id = %s", (yq.id,))
    result = cursor.fetchone()
    if result:
        print("YQ object already exists")
        return
    else:
        print("YQ object does not exist")
    cursor.execute("INSERT INTO yq (id, publisher, time, location, total, reserved) VALUES (%s, %s, %s, %s, %s, %s)", (yq.id, yq.publisher, yq.time, yq.location, yq.total, yq.reserved))
    db.commit()
    db.close()

# 从数据库中获取YQ对象
def get_yq(id):
    db = connect_to_database()
    cursor = db.cursor()
    cursor.execute("SELECT * FROM yq WHERE id = %s", (id,))
    result = cursor.fetchone()
    db.close()
    if result:
        return YQ(result[1], result[2], result[3], result[4], result[5])
    else:
        return None
# 从数据库中获取所有YQ对象
def get_all_yq():
    db = connect_to_database()
    cursor = db.cursor()
    cursor.execute("SELECT * FROM yq")
    results = cursor.fetchall()
    db.close()
    yqs = []
    for result in results:
        yqs.append(YQ(result[1], result[2], result[3], result[4], result[5]))
    return yqs
# 更新数据库中的YQ对象
def update_yq(yq, id):
    db = connect_to_database()
    cursor = db.cursor()
    cursor.execute("UPDATE yq SET id = %s, publisher = %s, time = %s, location = %s, total = %s, reserved = %s WHERE id = %s", (yq.id, yq.publisher, yq.time, yq.location, yq.total, yq.reserved, id))
    db.commit()
    db.close()

# 删除数据库中的YQ对象
def delete_yq(id):
    db = connect_to_database()
    cursor = db.cursor()
    cursor.execute("DELETE FROM yq WHERE id = %s", (id,))
    db.commit()
    db.close()
# 注册用户
def register_user(username, password):
    db = connect_to_database()
    cursor = db.cursor()
    cursor.execute("INSERT INTO yq_users (username1, password1) VALUES (%s, %s)", (username, password))
    db.commit()
    db.close()

# 创建Flask应用
app = Flask(__name__)

# 创建YQ表
create_table()
# 创建用户表
create_users_table()


# 添加YQ对象
@app.route('/yq/add', methods=['POST'])
def add_yq():
    data = request.get_json()
    yq = YQ(data['publisher'], data['time'], data['location'], data['total'], data['reserved'])
    save_yq(yq)
    return jsonify({'message': 'YQ object added successfully'})

# 获取YQ对象
@app.route('/yq/get_by_id/<string:id>', methods=['GET'])
def get_yq_by_id(id):
    yq = get_yq(id)
    if yq:
        return jsonify({'publisher': yq.publisher, 'time': yq.time, 'location': yq.location, 'total': yq.total, 'reserved': yq.reserved})
    else:
        return jsonify({'message': 'YQ object not found'})

# 获取所有YQ对象
@app.route('/yq/all', methods=['GET'])
def get_all_yq_api():
    yqs = get_all_yq()
    result = []
    for yq in yqs:
        result.append({'publisher': yq.publisher, 'time': yq.time, 'location': yq.location, 'total': yq.total, 'reserved': yq.reserved})
    return jsonify(result)

# 更新YQ对象
@app.route('/yq/update/<string:id>', methods=['POST'])
def update_yq_by_id(id):
    data = request.get_json()
    yq = get_yq(id)
    if yq:
        yq = YQ(data['publisher'], data['time'], data['location'], data['total'], data['reserved'])
        update_yq(yq, id)
        return jsonify({'message': 'YQ object updated successfully'})
    else:
        return jsonify({'message': 'YQ object not found'})


# 删除YQ对象
@app.route('/yq/delete/<string:id>', methods=['DELETE'])
def delete_yq_by_id(id):
    yq = get_yq(id)
    if yq:
        delete_yq(id)
        return jsonify({'message': 'YQ object deleted successfully'})
    else:
        return jsonify({'message': 'YQ object not found'})

# 注册接口
@app.route('/register', methods=['POST'])
def register():
    data = request.get_json()
    username = data.get('username')
    password = data.get('password')
    if not username or not password:
        return jsonify({'message': 'Missing username or password'}), 400
    register_user(username, password)
    return jsonify({'message': 'User registered successfully'}), 200

# 登录接口
@app.route('/login', methods=['POST'])
def login():
    # 获取 POST 请求中的表单数据
    data = request.form
    username = data.get('username')
    password = data.get('password')
    # 从数据库中查询user表
    db = connect_to_database()
    cursor = db.cursor()
    cursor.execute("SELECT * FROM yq_users WHERE username1 = %s", (username,))
    results = cursor.fetchall()
    db.close()
    # 检查用户是否存在
    if len(results) == 0:
        return jsonify({"message": "用户不存在"}), 401
    # 检查密码是否正确
    if results[0][1] != password:
        return jsonify({"message": "密码错误"}), 401
    return jsonify({"message": "登录成功"}), 200
# 运行Flask应用
if __name__ == '__main__':
    app.run()
